﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data; 

namespace StartStop.Helpers
{
    public class DetailedStatOverview
    {
        public const string LiveConnectionString = "data source=v2.startstop.me;initial catalog=startstop;persist security info=True;user id=sa;password=PASSWORD;";

        public static ServiceResources.MessageResponse FindOverview(string Title, StartStop.ServiceResources.StatOverView.OverviewType OverviewType)
        {
            StartStop.ServiceResources.MessageResponse oReturn = new ServiceResources.MessageResponse();
            oReturn.Success = false;

            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(); 
            SqlCommand _cmdupdatecheck = new SqlCommand("select OverviewID from detailstatoverview where title=@title and overviewtype=@overviewtype", conn);
            _cmdupdatecheck.Parameters.AddWithValue("@Title", Title);
            _cmdupdatecheck.Parameters.AddWithValue("@OverviewType", (int)OverviewType);
           
            if (_cmdupdatecheck.ExecuteScalar() != null)
            {
                // the overview has been found
                oReturn.Message = "Overview Found";
                oReturn.ReturnID = int.Parse(_cmdupdatecheck.ExecuteScalar().ToString());
                oReturn.Success = true;
            }

            conn.Close(); 
            return oReturn; 
        }

        public static ServiceResources.MessageResponse UpdateOverview (ref ServiceResources.StatOverView oOverview)
        {

            StartStop.ServiceResources.MessageResponse oReturn = new ServiceResources.MessageResponse();
            oReturn.Success = false; 
            bool _isupdate = false; // Is an overview being updated or inserted.

            try
            {
                SqlConnection conn = new SqlConnection(LiveConnectionString);
                conn.Open();

                SqlCommand cmd = new SqlCommand(); 
                
                // TODO :   Make sure we have all fields saved.
                //          Complete the update! 

                SqlCommand _cmdupdatecheck = new SqlCommand("select OverviewID from detailstatoverview where title=@title and overviewtype=@overviewtype", conn);
                _cmdupdatecheck.Parameters.AddWithValue("@Title", oOverview.Title);
                _cmdupdatecheck.Parameters.AddWithValue("@OverviewType", (int)oOverview.TypeOfOverview);
                string _updatecheck = "";

                if (_cmdupdatecheck.ExecuteScalar() != null)
                {
                    _updatecheck = _cmdupdatecheck.ExecuteScalar().ToString(); 
                }
                if (_updatecheck != "")
                {
                    // This is an update
                    cmd = new SqlCommand(@"
                                            update DetailStatOverview set
                                            Title = @Title, 
                                            Description = @Description, 
                                            ModifiedOn = @ModifiedOn, 
                                            MoreInfoURL = @MoreInfoURL, 
                                            OVerviewType = @OverviewType, 
                                            DeveloperID = @DeveloperID 

                                            where OverviewID = @OverviewID 

                                            ", conn);
                    _isupdate = true; 

                    // These are the update paramters. 
                    cmd.Parameters.AddWithValue("@OverviewID", long.Parse(_updatecheck));  
                   
                    cmd.Parameters.AddWithValue("@ModifiedOn", DateTime.Now);

                    // Set the overviewID
                    oOverview.OverviewID = long.Parse(_updatecheck); 

                }
                else
                {
                    cmd = new SqlCommand(@"
                    insert into  DetailStatOverview 
                    (Title, Description, CreatedOn, MoreInfoURL,OverviewType, DeveloperID) Values
                    (@Title, @Description, @CreatedOn, @MoreInfoURL, @OverviewType, @DeveloperID)
                ; select @@IDENTITY;", conn);
                }


                #region Add Parameters
                cmd.Parameters.AddWithValue("@Title", oOverview.Title);
                cmd.Parameters.AddWithValue("@Description", oOverview.Description);
                cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
                if (oOverview.MoreInfoURL == null)
                {
                    cmd.Parameters.AddWithValue("@MoreInfoURL", System.DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@MoreInfoURL", oOverview.MoreInfoURL);
                }
                cmd.Parameters.AddWithValue("@OverviewType", (int)oOverview.TypeOfOverview);

                if (oOverview.DeveloperID.ToString() != "")
                {
                    cmd.Parameters.AddWithValue("@DeveloperID", oOverview.DeveloperID); 
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DeveloperID", System.DBNull.Value);
                }
                #endregion 

                if (!_isupdate) // Check to see if it is not an update
                {
                    oOverview.OverviewID = long.Parse(cmd.ExecuteScalar().ToString());
                    oReturn.ReturnID = oOverview.OverviewID; 
                    oReturn.Success = true; 
                }
                else
                {
                    cmd.ExecuteNonQuery();
                    oReturn.ReturnID = oOverview.OverviewID;
                    oReturn.Success = true; 
                }
                
            }
            catch (Exception ex)
            {
                ServiceResources.ErrorEvent oError = new ServiceResources.ErrorEvent();
                oError.ErrorDetail = ex.Message.ToString();
                oError.ErrorLevel = ServiceResources.ErrorEvent.ErrorLevelType.Critical;
                oError.ErrorMessage = "Cannot update stat overview";
                oError.Method = "Helpers.DetailedStatOverview"; 

                Helpers.EventLog.LogError(oError); 

            }
            //TODO: Load in the GUID as well. 

            return oReturn; 
        }


        #region Load overviews
        public static StartStop.ServiceResources.StatOverView LoadOverview(Int64 OverviewID)
        {
            StartStop.ServiceResources.StatOverView oOverview = new ServiceResources.StatOverView();

            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(@"
                        select top 1 * from DetailStatOverview where OverviewID = @OverviewID
            ", conn);
            cmd.Parameters.AddWithValue("@OverviewID", OverviewID);

            DataTable _returnDataTable = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);
            foreach (DataRow oRow in _returnDataTable.Rows)
            {

                // TODO : Complete all the fields here. 
                oOverview.Title = oRow["Title"].ToString();
                oOverview.MoreInfoURL = oRow["MoreInfoURL"].ToString();
                oOverview.OverviewID = Int64.Parse(oRow["OverviewID"].ToString());
                oOverview.Description = oRow["Description"].ToString(); 
            }

            return oOverview; 
        }

        public static List<ServiceResources.StatOverView> ReturnListOfOverviewStatsUserHasNoRecordsFor(Int64 UserID)
        {
            List<ServiceResources.StatOverView> oList = new List<ServiceResources.StatOverView>();

            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();

            // This needs to be extended so that it doesn't pick up other types (for now it just avoids PS3 a.OverviewType <> 3)

            SqlCommand cmd = new SqlCommand(@"
                        SELECT     a.OverviewGUID, a.Title, a.MoreInfoURL
                        FROM       DetailStatOverview AS a
                        WHERE      NOT EXISTS (SELECT * FROM DetailStatUserLog AS b WHERE b.OverViewGUID = a.OverviewGUID and b.UserID =@UserID) and a.OverviewType <> 3
            ", conn);
            cmd.Parameters.AddWithValue("@UserID", UserID); 

            DataTable _returnDataTable = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            ServiceResources.StatOverView oOverview = new ServiceResources.StatOverView(); 

            
            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                oOverview = new ServiceResources.StatOverView();
                if (oRow["MoreInfoURL"].ToString() != "")
                {
                    oOverview.MoreInfoURL = oRow["MoreInfoURL"].ToString(); 
                }
                oOverview.Title = oRow["Title"].ToString();
                oOverview.OverviewGUID = Guid.Parse(oRow["OverviewGUID"].ToString());
                oList.Add(oOverview); 
            }

            return oList;
        }

        public static StartStop.ServiceResources.MessageResponse FindOverviewIDFromTitle(string OverviewTitle)
        {
            StartStop.ServiceResources.MessageResponse oResponse = new ServiceResources.MessageResponse();
            oResponse.Success = false;

            string _result = Sql.ExecuteScalar("select OverviewID from DetailStatOverview where Title = @passedvalue", OverviewTitle);
            if (_result != "")
            {
                oResponse.Success = true;
                oResponse.ReturnID = long.Parse(_result);
                oResponse.Message = "Found your overviewID"; 
            }

            return oResponse; 
        }

        #endregion 
    }
}
